package com.ydd.app.dao.impl;

import java.util.ArrayList;
import java.util.List;

import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.ydd.app.bean.common.NewsDetailBean;
import com.ydd.app.bean.common.NewsTypeBean;
import com.ydd.app.bean.common.ReviewStatusBean;
import com.ydd.app.dao.NewsDao;
import com.ydd.common.hibernate3.JdbcBaseDao;
import com.ydd.common.util.CommonUtil;

@Repository
public class NewsDaoImpl  extends JdbcBaseDao  implements NewsDao{
	
	public String SELECT_LAST_INSERT_ID() {
		String a = null;
		SqlRowSet set = getJdbcTemplate().queryForRowSet("SELECT IDENT_CURRENT('news_detail_mst') as id");
		while(set.next()){
			System.out.println(set.getString("id"));
			a = set.getString("id");
		}
		System.out.println(a);
		return a;
	}
	
	@Override
	public NewsDetailBean queryNewsDetail(final String newsId) {
		SqlRowSet set = getJdbcTemplate().queryForRowSet(NewsDaoSql.QUERY_NEWS_DETAIL_SQL, newsId);
		while(set.next()){
			final NewsDetailBean news = new NewsDetailBean();
			final NewsTypeBean newsType = new NewsTypeBean();
			final ReviewStatusBean status = new ReviewStatusBean();
			news.setCreateTime(set.getString("create_time"));
			news.setFormatCreateTime(set.getString("create_time"));
			news.setFormatUpdateTime(set.getString("update_time"));
			news.setNewsDetail(set.getString("news_detail"));
			news.setNewsDescribe(set.getString("news_describe"));
			news.setPictureUrl(set.getString("picture_url"));
			news.setNewsId(set.getString("news_id"));
			news.setTitle(set.getString("title"));
			news.setUpdateTime(set.getString("update_time"));
			status.setReviewStatusName(set.getString("update_time"));
			status.setReviewStatusId(set.getString("news_status"));
			status.setReviewTime(set.getString("review_time"));
			status.setReason(set.getString("review_reason"));
			newsType.setNewsTypeId(set.getString("news_type_id"));
			newsType.setNewsTypeName(set.getString("type_name"));
			news.setNewsType(newsType);
			news.setReviewStatus(status);
			return news;
		}
		return null;
	}

	@Override
	public NewsDetailBean updateNews(final String newsId, final String newsDetail,
			String title, String newsTypeId,final String newsDescribe,final String pictureUrl) {
		int row = getJdbcTemplate().update(NewsDaoSql.UPDATE_NEWS_SQL, title,newsDetail,newsTypeId,newsDescribe,pictureUrl,newsId);
		if( row > 0 ) {
			return queryNewsDetail(newsId);
		}
		return null;
	}

	@Override
	public boolean deleteNews(final String newsId) {
		return getJdbcTemplate().update(NewsDaoSql.DELETE_NEWS_SQL,newsId) > 0 ;
	}

	@Override
	@Transactional
	public boolean createNews(final String newsDetail, final String title,
			final String newsTypeId ,final String userId,final String newsDescribe,final String pictureUrl) {
		int row = getJdbcTemplate().update(NewsDaoSql.INSERT_NEWS_SQL, title,newsDetail,userId,newsTypeId,newsDescribe,pictureUrl);
		if( row > 0 ) {
			return true;
//				String id = SELECT_LAST_INSERT_ID();
//				if ( null != id  ) {
//					return queryNewsDetail(String.valueOf(id));
//				}
		}
		return false;
	}

	@Override
	public List<NewsDetailBean> queryNewsInBackstage(final int page, final int pageCount,
			final String newsTypeId,final String newsStatusId) {
		SqlRowSet set = getJdbcTemplate().queryForRowSet(NewsDaoSql.queryNewsInBackgroundSql(page, pageCount, newsTypeId, newsStatusId));
		final List<NewsDetailBean> news = new ArrayList<NewsDetailBean>();
		while(set.next()){
			final NewsDetailBean newsDetail = new NewsDetailBean();
			final NewsTypeBean newsType = new NewsTypeBean();
			final ReviewStatusBean status = new ReviewStatusBean();
			newsDetail.setCreateTime(set.getString("create_time"));
			newsDetail.setFormatCreateTime(set.getString("create_time"));
			newsDetail.setFormatUpdateTime(set.getString("update_time"));
			newsDetail.setNewsId(set.getString("news_id"));
			newsDetail.setPictureUrl(set.getString("picture_url"));
			newsDetail.setNewsDescribe(set.getString("news_describe"));
			newsDetail.setTitle(set.getString("title"));
			newsDetail.setUpdateTime(set.getString("update_time"));
			status.setReviewStatusName(set.getString("update_time"));
			status.setReviewStatusId(set.getString("news_status"));
			newsType.setNewsTypeId(set.getString("news_type_id"));
			newsType.setNewsTypeName(set.getString("type_name"));
			newsDetail.setNewsType(newsType);
			newsDetail.setReviewStatus(status);
			news.add(newsDetail);
		}
		if( null != news && news.size() > 0 ) {
			return news;
		}
		else {
			return null;
		}
	}

	@Override
	public int queryNewsCountInBackstage(final String newsTypeId,final String newsStatusId) {
		final StringBuilder sb = new StringBuilder();
		sb.append(" SELECT " 
				+" count(newsd.news_id) as count  "
				+" FROM  "
				+" news_detail_mst as newsd " 
				+" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
				+" where " 
				+"  news.is_delete = '0' " 
				+"  and newsd.is_delete = '0' " );
		if( CommonUtil.isNotEmpty(newsStatusId)) {
			sb.append(" and newsd.news_status = " +newsStatusId);
		}
		if(CommonUtil.isNotEmpty(newsTypeId)) {
			sb.append(" and news.news_type_id = "+newsTypeId );
		}
		final List<Integer> counts = getJdbcTemplate().queryForList(sb.toString(), Integer.class );
		if( null != counts && counts.size() > 0 ) {
			return counts.get(0);
		}
		else {
			return 0;
		}
	}

	@Override
	public NewsDetailBean reviewNews(final String newsId,final String statusId,final String reson,final String userId) {
		int row = getJdbcTemplate().update(NewsDaoSql.REWIEW_NEWS_SQL, statusId,reson,userId,newsId);
		if( row > 0 ) {
			return queryNewsDetail(newsId);
		}
		return null;
	}

	@Override
	public List<NewsTypeBean> queryNewsTypes() {
		SqlRowSet set = getJdbcTemplate().queryForRowSet(NewsDaoSql.QUERY_NEWS_TYPES_SQL);
		final List<NewsTypeBean> news = new ArrayList<NewsTypeBean>();
		while(set.next()){
			final NewsTypeBean newsType = new NewsTypeBean();
			newsType.setNewsTypeId(set.getString("news_type_id"));
			newsType.setNewsTypeName(set.getString("type_name"));
			news.add(newsType);
		}
		if( null != news && news.size() > 0 ) {
			return news;
		}
		else {
			return null;
		}
	}

	@Override
	public List<NewsDetailBean> queryNewsInWeb(int page, int pageCount,
			String newsTypeId, String keyWord) {
		SqlRowSet set = getJdbcTemplate().queryForRowSet(NewsDaoSql.queryNewsInBackgroundInWeb(page, pageCount, newsTypeId, keyWord));
		final List<NewsDetailBean> news = new ArrayList<NewsDetailBean>();
		while(set.next()){
			final NewsDetailBean newsDetail = new NewsDetailBean();
			final NewsTypeBean newsType = new NewsTypeBean();
			final ReviewStatusBean status = new ReviewStatusBean();
			newsDetail.setCreateTime(set.getString("create_time"));
			newsDetail.setFormatCreateTime(set.getString("create_time"));
			newsDetail.setFormatUpdateTime(set.getString("update_time"));
			newsDetail.setNewsId(set.getString("news_id"));
			newsDetail.setTitle(set.getString("title"));
			newsDetail.setPictureUrl(set.getString("picture_url"));
			newsDetail.setNewsDescribe(set.getString("news_describe"));
			newsDetail.setUpdateTime(set.getString("update_time"));
			status.setReviewStatusName(set.getString("update_time"));
			status.setReviewStatusId(set.getString("news_status"));
			newsType.setNewsTypeId(set.getString("news_type_id"));
			newsType.setNewsTypeName(set.getString("type_name"));
			newsDetail.setNewsType(newsType);
			newsDetail.setReviewStatus(status);
			news.add(newsDetail);
		}
		if( null != news && news.size() > 0 ) {
			return news;
		}
		else {
			return null;
		}
	}

	@Override
	public int queryNewsCountInWeb(String newsTypeId) {
		final StringBuilder sb = new StringBuilder();
		sb.append( " SELECT "
		              +" count(newsd.news_id) as count  "
		              +" FROM  "
		              +" news_detail_mst as newsd " 
		              +" inner join news_type_mst as news on news.news_type_id = newsd.news_type_id "
		              +" where " 
		              +"  news.is_delete = '0' " 
		              +"  and newsd.is_delete = '0' " );
		              if (CommonUtil.isNotEmpty(newsTypeId) ) {
		            	  sb.append("and news.news_type_id = "+newsTypeId );
		              }
		final List<Integer> counts = getJdbcTemplate().queryForList(sb.toString(), Integer.class );
		if( null != counts && counts.size() > 0 ) {
			return counts.get(0);
		}
		else {
			return 0;
		}
	}
	
	@Override
	public List<NewsTypeBean> queryNewestNews(final int pageCount) {
		final List<NewsTypeBean> newsTypes = queryNewsTypes();
		String sign = "";
		StringBuilder sb = new StringBuilder();
		sb.append(" select * from ( ");
		for (NewsTypeBean newsType : newsTypes) {
			if( null != newsType ) {
				sb.append(sign);
				sb.append(NewsDaoSql.queryNewsInBackgroundWhenNewest(pageCount)
						+"and news.news_type_id = "+newsType.getNewsTypeId() );
				sign = "union all";
			}
		}
		sb.append(" ) as a order by a.news_type_id asc, update_time desc");
		SqlRowSet set = getJdbcTemplate().queryForRowSet(sb.toString());
		final List<NewsDetailBean> news = new ArrayList<NewsDetailBean>();
		while(set.next()){
			final NewsDetailBean newsDetail = new NewsDetailBean();
			final NewsTypeBean newsType = new NewsTypeBean();
			final ReviewStatusBean status = new ReviewStatusBean();
			newsDetail.setCreateTime(set.getString("create_time"));
			newsDetail.setFormatCreateTime(set.getString("create_time"));
			newsDetail.setFormatUpdateTime(set.getString("update_time"));
			newsDetail.setNewsDetail(set.getString("news_detail"));
			newsDetail.setNewsId(set.getString("news_id"));
			newsDetail.setPictureUrl(set.getString("picture_url"));
			newsDetail.setNewsDescribe(set.getString("news_describe"));
			newsDetail.setTitle(set.getString("title"));
			newsDetail.setUpdateTime(set.getString("update_time"));
			status.setReviewStatusName(set.getString("update_time"));
			status.setReviewStatusId(set.getString("news_status"));
			newsType.setNewsTypeId(set.getString("news_type_id"));
			newsType.setNewsTypeName(set.getString("type_name"));
			newsDetail.setNewsType(newsType);
			newsDetail.setReviewStatus(status);
			news.add(newsDetail);
		}
		if( null != news && news.size() > 0 ) {
			for (NewsTypeBean newsType : newsTypes) {
				if( null != newsType ) {
					final List<NewsDetailBean> newNews = new ArrayList<NewsDetailBean>();
					for (int i = 0; i < news.size(); i++) {
						final NewsDetailBean newsDetail = news.get(i);
						if( null != newsDetail ) {
							if( newsType.getNewsTypeId().equals(newsDetail.getNewsType().getNewsTypeId())) {
								newNews.add(newsDetail);
							}
						}
					}
					if( null != newNews && newNews.size() > 0 ) {
						newsType.setNews(newNews.toArray(new NewsDetailBean[0]));
					}
				}
			}
			return newsTypes;
		}
		else {
			return null;
		}
	}

	@Override
	public boolean querythisnewsTypeIdIsExist(final String newsTypeId) {
		final String sql = "select count(*) as count from news_type_mst where news_type_id = ? "; 
		final List<Integer> counts = getJdbcTemplate().queryForList(sql,Integer.class,newsTypeId);
		if( null != counts && counts.size() > 0 )  {
			if( counts.get(0) > 0 ) {
				return true;
			}
		}
		return false;
	}

}
